/* 관리자 계정 */

insert into seller values('admin', 'admin', '관리자', '111-111', '서울시 강남구 삼성동', '코엑스', '010-1111-2222', 'admin@naver.com', sysdate);

/* 판매자 계정 */

insert into seller values('seller1', 'seller1', '판매자1', '111-111', '서울시 강남구 삼성동', '코엑스', '010-1111-2223', 'seller1@naver.com', sysdate);
insert into seller values('seller2', 'seller2', '판매자2', '111-111', '서울시 강남구 삼성동', '코엑스', '010-1111-2224', 'seller2@naver.com', sysdate);
insert into seller values('seller3', 'seller3', '판매자3', '111-111', '서울시 강남구 삼성동', '코엑스', '010-1111-2225', 'seller3@naver.com', sysdate);

/* 소비자 계정 */

insert into customer values('customer', '1234', '이민선', '111-111', '서울특별시 삼성동', '무역센타4층', '010-1234-5678', 'customer@kita.net', sysdate);
insert into customer values('customer2', '1234', '문연지', '111-111', '서울특별시 삼성동', '무역센타4층', '010-1234-5678', 'customer@kita.net', sysdate); 

/* 카테고리 */

insert into category values('f','과일류');
insert into category values('v','채소류');
insert into category values('i','생선류');
insert into category values('m','육류');
insert into category values('g','식료품류');
insert into category values('h','건강용품');
insert into category values('a','패션');
insert into category values('l','생활용품');
insert into category values('e','기타');

/* 가게 */

insert into shop values(SEQ_shop_shop_seq.nextval, 'seller1', 'f', '민선이', '111-111', '서울시 강남구 삼성동', '코엑스', '010-1111-2224');
insert into shop values(SEQ_shop_shop_seq.nextval, 'seller2', 'l', '다이소', '111-111', '서울시 강남구 삼성동', '코엑스', '010-1111-2225');
insert into shop values(SEQ_shop_shop_seq.nextval, 'seller3', 'f', '다이', '111-111', '서울시 강남구 삼성동', '코엑스', '010-1111-2226');

/* 상품 */


select * from product;


insert into product values(SEQ_product_product_seq.nextval, 6, 'f', '사과', 1000, EMPTY_CLOB(), EMPTY_CLOB(), '크크', '키키', 10, sysdate);
insert into product values(SEQ_product_product_seq.nextval, 6, 'f', '배', 1000, EMPTY_CLOB(), EMPTY_CLOB(), '크크', '키키', 10, sysdate);
insert into product values(SEQ_product_product_seq.nextval, 6, 'f', '포도', 1000, EMPTY_CLOB(), EMPTY_CLOB(), '크크', '키키', 10, sysdate);
insert into product values(SEQ_product_product_seq.nextval, 6, 'f', '키위', 1000, EMPTY_CLOB(), EMPTY_CLOB(), '크크', '키키', 10, sysdate);
insert into product values(SEQ_product_product_seq.nextval, 6, 'f', '블루베리', 1000, EMPTY_CLOB(), EMPTY_CLOB(), '크크', '키키', 10, sysdate);

insert into product values(SEQ_product_product_seq.nextval, 3, 'l', '장미칼', 10000, EMPTY_CLOB(), EMPTY_CLOB(), '크크', '키키', 10, sysdate);
insert into product values(SEQ_product_product_seq.nextval, 3, 'l', '도마', 5000, EMPTY_CLOB(), EMPTY_CLOB(), '크크', '키키', 10, sysdate);

/* 카트 */

insert into cart values('customer', 16, 6); 
insert into cart values('customer', 17, 7);

/* 상품 주문 */

insert into product_order values(SEQ_product_order_order_seq.nextval, 'customer', 'y', 'd', sysdate);

/* 상품 상세 */

insert into order_detail values(16, 81, 6);
insert into order_detail values(17, 82, 7);

/* 시장통계 */

/* 1. 당일 민선이의 총매출을 구하시오 */

select s.name 가게이름, sum(p.product_price * d.quantity) 총매출 
from product p, product_order o, order_detail d, shop s
where p.product_seq = d.product_seq
and p.shop_seq = s.shop_seq
and o.order_seq = d.order_seq
and s.name = '파오파오'
and o.inputdate like sysdate
group by s.name;

select s.name name, sum(p.product_price * d.quantity) total 
from product p, product_order o, order_detail d, shop s
where p.product_seq = d.product_seq
and p.shop_seq = s.shop_seq
and o.order_seq = d.order_seq
and s.shop_seq = 3
and o.inputdate like sysdate
group by s.name;

/* 2. 일주간 민선이의 총매출을 구하시오 */

select s.name 가게이름, sum(p.product_price * d.quantity) 총매출 
from product p, product_order o, order_detail d, shop s
where p.product_seq = d.product_seq
and p.shop_seq = s.shop_seq
and o.order_seq = d.order_seq
and s.name = '민선이네 과일가게'
and o.inputdate between sysdate - 7 and sysdate
group by s.name;

/* 3. 특정기간 민선이의 총매출을 구하시오 */

select s.name 가게이름, sum(p.product_price * d.quantity) 총매출 
from product p, product_order o, order_detail d, shop s
where p.product_seq = d.product_seq
and p.shop_seq = s.shop_seq
and o.order_seq = d.order_seq
and s.name = '민선이네 과일가게'
and o.inputdate between '14/10/01' and sysdate
group by s.name;

/* 4. 당일 민선이의 판매목록을 구하시오 */

select p.product_name 상품이름, p.product_price 상품가격, d.quantity 수량, c.name 소비자
from product p, product_order o, order_detail d, customer c, shop s
where p.product_seq = d.product_seq
and p.shop_seq = s.shop_seq
and o.order_seq = d.order_seq
and o.customer_id = c.customer_id
and s.name = '민선이네 과일가게'
and o.inputdate like sysdate
order by c.name;

/* 5. 일주간 민선이의 판매목록을 구하시오 */

select p.product_name 상품이름, p.product_price 상품가격, d.quantity 수량, c.name 소비자
from product p, product_order o, order_detail d, customer c, shop s
where p.product_seq = d.product_seq
and p.shop_seq = s.shop_seq
and o.order_seq = d.order_seq
and o.customer_id = c.customer_id
and s.name = '민선이네 과일가게'
and o.inputdate between sysdate - 7 and sysdate
order by c.name;

/* 6. 특정기간 민선이의 판매목록을 구하시오 */

select p.product_name 상품이름, p.product_price 상품가격, d.quantity 수량, c.name 소비자
from product p, product_order o, order_detail d, customer c, shop s
where p.product_seq = d.product_seq
and p.shop_seq = s.shop_seq
and o.order_seq = d.order_seq
and o.customer_id = c.customer_id
and s.name = '민선이네 과일가게'
and o.inputdate between '14/10/01' and sysdate
order by c.name;

/* 7. 당일 카테고리 f의 총매출을 구하시오 */

select y.category_key category, sum(p.product_price * d.quantity) total
from product p, product_order o, order_detail d, customer c, category y, shop s 
where p.product_seq = d.product_seq
and p.shop_seq = s.shop_seq
and o.order_seq = d.order_seq
and o.customer_id = c.customer_id
and y.category_key = s.category
and y.category_key = p.product_category
and y.category_key = 'i'
and o.inputdate like sysdate
group by y.category_key;

/* 8. 일주간 카테고리 f의 총매출을 구하시오 */

select y.category_key category, sum(p.product_price * d.quantity) total
from product p, product_order o, order_detail d, customer c, category y, shop s 
where p.product_seq = d.product_seq
and p.shop_seq = s.shop_seq
and o.order_seq = d.order_seq
and o.customer_id = c.customer_id
and y.category_key = s.category
and y.category_key = p.product_category
and y.category_key = 'i'
and o.inputdate between sysdate - 7 and sysdate
group by y.category_key;

/* 9. 특정기간 카테고리 f의 총매출을 구하시오 */

select y.category_key category, sum(p.product_price * d.quantity) total
from product p, product_order o, order_detail d, customer c, category y, shop s 
where p.product_seq = d.product_seq
and p.shop_seq = s.shop_seq
and o.order_seq = d.order_seq
and o.customer_id = c.customer_id
and y.category_key = s.category
and y.category_key = p.product_category
and y.category_key = 'f'
and o.inputdate between '14/10/01' and sysdate
group by y.category_key;

/* 10. 당일 카테고리 f의 판매목록을 구하시오 */

select p.product_name 상품이름, p.product_price 상품가격, d.quantity 수량, c.name 소비자
from product p, product_order o, order_detail d, customer c, category y, shop s 
where p.product_seq = d.product_seq
and p.shop_seq = s.shop_seq
and o.order_seq = d.order_seq
and o.customer_id = c.customer_id
and y.category_key = s.category
and y.category_key = p.product_category
and y.category_key = 'f'
and o.inputdate like sysdate
order by c.name;

/* 11. 일주간 카테고리 f의 판매목록을 구하시오 */

select y.category_key category, p.product_name product_name, p.product_price product_price, d.quantity quantity, s.name shop_name, d.order_seq order_seq, to_char(o.inputdate, 'YYYY-MM-DD') inputdate
from product p, product_order o, order_detail d, category y, shop s 
where p.product_seq = d.product_seq
and y.category_key = p.product_category
and p.shop_seq = s.shop_seq
and o.order_seq = d.order_seq
and y.category_key = 'h'
and o.inputdate between sysdate - 7 and sysdate
order by d.order_seq;

/* 12. 특정기간 카테고리 h의 판매목록을 구하시오 */

select p.product_name 상품이름, p.product_price 상품가격, d.quantity 수량, c.name 소비자
from product p, product_order o, order_detail d, customer c, category y, shop s 
where p.product_seq = d.product_seq
and p.shop_seq = s.shop_seq
and o.order_seq = d.order_seq
and o.customer_id = c.customer_id
and y.category_key = s.category
and y.category_key = p.product_category
and y.category_key = 'h'
and o.inputdate between '14/10/01' and sysdate
order by c.name;

/* 13. 일주간 상점의 판매목록(예전버전) */
<select id="shopDay" resultType="hashmap" parameterType="int">
select p.product_name product_name, p.product_price product_price, d.quantity quantity, s.shop_seq shop_seq, c.customer_id customer_id, d.order_seq order_seq, to_char(o.inputdate, 'YYYY-MM-DD') inputdate
from product p, product_order o, order_detail d, customer c, shop s
where p.product_seq = d.product_seq
and p.shop_seq = s.shop_seq
and o.order_seq = d.order_seq
and o.customer_id = c.customer_id
and s.shop_seq = #{shop_seq}
and o.inputdate like sysdate
order by p.product_name desc, c.customer_id, d.order_seq asc
</select>

/* 14. 당일 상점의 판매목록(수정버전) */
select p.product_name product_name, p.product_price product_price, sum(d.quantity) quantity, sum(d.quantity) * p.product_price volume, s.name shop_name, s.shop_seq shop_seq, to_char(o.inputdate, 'YYYY-MM-DD') inputdate
from product p, order_detail d, product_order o, shop s
where p.product_seq = d.product_seq
and o.order_seq = d.order_seq
and p.shop_seq = s.shop_seq
and s.shop_seq = 6
group by d.product_seq, p.product_price, p.product_name, s.name, s.shop_seq, o.inputdate
order by sum(d.quantity) * p.product_price desc;

/* 가게별 당일 매출량 */
select p.product_name product_name, p.product_price product_price, sum(d.quantity) quantity, sum(d.quantity) * p.product_price volume
from product p, order_detail d, product_order o, shop s
where p.product_seq = d.product_seq
and o.order_seq = d.order_seq
and p.shop_seq = s.shop_seq
and s.shop_seq = 6
and o.inputdate like sysdate
group by d.product_seq, p.product_price, p.product_name, s.name, s.shop_seq
order by sum(d.quantity) * p.product_price desc;

/* 가게별 일주일 매출량 */
select p.product_name product_name, p.product_price product_price, sum(d.quantity) quantity, sum(d.quantity) * p.product_price volume
from product p, order_detail d, product_order o, shop s
where p.product_seq = d.product_seq
and o.order_seq = d.order_seq
and p.shop_seq = s.shop_seq
and s.shop_seq = 6
and o.inputdate between sysdate - 7 and sysdate
group by d.product_seq, p.product_price, p.product_name, s.name, s.shop_seq
order by sum(d.quantity) * p.product_price desc;

/* 카테고리별 당일 매출량  */
select y.category_key category_key, p.product_name product_name, p.product_price product_price, sum(d.quantity) quantity, sum(d.quantity) * p.product_price volume
from product p, order_detail d, product_order o, shop s, category y
where p.product_seq = d.product_seq
and o.order_seq = d.order_seq
and p.shop_seq = s.shop_seq
and y.category_key = s.category
and y.category_key = p.product_category
and y.category_key = 'f'
and o.inputdate like sysdate
group by d.product_seq, p.product_price, p.product_name, s.name, s.shop_seq, y.category_key
order by sum(d.quantity) * p.product_price desc;

/* 카테고리별 일주일 매출량  */
select y.category_key category_key, p.product_name product_name, p.product_price product_price, sum(d.quantity) quantity, sum(d.quantity) * p.product_price volume
from product p, order_detail d, product_order o, shop s, category y
where p.product_seq = d.product_seq
and o.order_seq = d.order_seq
and p.shop_seq = s.shop_seq
and y.category_key = s.category
and y.category_key = p.product_category
and y.category_key = 'f'
and o.inputdate between sysdate - 7 and sysdate
group by d.product_seq, p.product_price, p.product_name, s.name, s.shop_seq, y.category_key
order by sum(d.quantity) * p.product_price desc;

/* 월별로 매출량 뿌리기 */

select to_char(inputdate, 'YYYY-MM-DD') 
from product_order
where inputdate between sysdate -31 and sysdate;
	


/* 한 가게당 총매출 */



select name,
select s.name, (select sum(d.quantity) * p.product_price
from product p, order_detail d, product_order o, shop s
where p.product_seq = d.product_seq
and o.order_seq = d.order_seq
and p.shop_seq = s.shop_seq
and o.inputdate like sysdate
group by s.name, d.product_seq, p.product_price)
order by s.name;

select sum(d.quantity) * p.product_price
from product p, order_detail d, product_order o, shop s
where p.product_seq = d.product_seq
and o.order_seq = d.order_seq
and p.shop_seq = s.shop_seq
and s.name = '민선이네과일가게'
and o.inputdate like sysdate
group by d.quantity, p.product_price;

/* 장돌뱅이 월별 총매출 그래프 현황 */

select to_char(o.inputdate, 'YYYY-MM') inputdate, s.category category, sum(p.product_price * d.quantity) volume
from product p, product_order o, order_detail d, shop s
where p.product_seq = d.product_seq
and p.shop_seq = s.shop_seq
and o.order_seq = d.order_seq
group by to_char(o.inputdate, 'YYYY-MM'), s.category
order by to_char(o.inputdate, 'YYYY-MM');

select s.category category, sum(p.product_price * d.quantity) volume
from product p, product_order o, order_detail d, shop s
where p.product_seq = d.product_seq
and p.shop_seq = s.shop_seq
and o.order_seq = d.order_seq
group by s.category;

update product_order set inputdate = '14/07/29' where order_seq = 45;


/* 일별 변화량 
 * */
select p.product_name product_name, p.product_price product_price, s.shop_seq, s.name shop_name, sum(d.quantity) quantity, sum(d.quantity) * p.product_price volume
from product p, order_detail d, product_order o, shop s
where p.product_seq = d.product_seq
and o.order_seq = d.order_seq
and p.shop_seq = s.shop_seq
and s.shop_seq = 1
and o.inputdate like sysdate
group by d.product_seq, p.product_price, p.product_name, s.name, s.shop_seq
order by sum(d.quantity) * p.product_price desc;

select to_char(o.inputdate, 'YYYY-MM-DD') month, sum(p.product_price * d.quantity) volume
from product p, product_order o, order_detail d, shop s
where p.product_seq = d.product_seq
and p.shop_seq = s.shop_seq
and o.order_seq = d.order_seq
and s.shop_seq = 1
and o.inputdate between sysdate - 7 and sysdate
group by to_char(o.inputdate, 'YYYY-MM-DD')
order by to_char(o.inputdate, 'YYYY-MM-DD');
